package com.neusoft.neuiotms.dcam.fixed.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.neusoft.neuiotms.dcam.fixed.model.InventoryResultTemporary;

public interface InventoryResultTemporaryRepository extends JpaRepository<InventoryResultTemporary, Long> {

    //    void deleteByInventoryDuty_Id(long id);

    @Query(value = "SELECT nirt.id AS id, nfa.name AS name, nfa.code AS code,nfa.original_code AS originalCode,nfa.purchase_date AS purchaseDate,nfa.supplier_id AS supplierId,nfa.supplier_name AS supplierName,nfa.specifications AS specifications,nfa.brand AS brand,nfa.serialnumber AS serialnumber,nfa.cabinet AS cabinet,nfa.maintenance_start_time AS maintenanceStartTime,nfa.maintenance_end_time AS maintenanceEndTime,nfa.location AS location,nfa.location_name AS locationName,nfa.big_type_name AS bigTypeName,nfa.big_type_code AS bigTypeCode,nfa.middle_type_name AS middleTypeName,nfa.middle_type_code AS middleTypeCode,nfa.small_type_name AS smallTypeName,nfa.small_type_code AS smallTypeCode,nfa.contract_no AS contractNo,nfa.contract_name AS contractName,nfa.measure_unit AS measureUnit,nfa.amount AS amount,nfa.money AS money,nfa.use_period AS usePeriod,nfa.management_org_name AS managementOrgName,nfa.management_org_code AS managementOrgCode,nfa.management_user_code AS managementUserCode,nfa.management_user_name AS managementUserName,nfa.belongs AS belongs,nfa.categories AS categories,nfa.card AS card,nfa. STATUS AS status,nfa.purpose AS purpose FROM neuiot_inventory_result_temporary nirt LEFT JOIN neuiot_fixed_asset nfa ON nirt.asset_id = nfa.id WHERE nirt.inventory_duty_id = :id", nativeQuery = true)
    List<Map<String, Object>> list(@Param("id") Long id);

    @Query(value = "SELECT nirt.id AS id, nfa.name AS name, nfa.code AS code,nfa.original_code AS originalCode,nfa.purchase_date AS purchaseDate,nfa.supplier_id AS supplierId,nfa.supplier_name AS supplierName,nfa.specifications AS specifications,nfa.brand AS brand,nfa.serialnumber AS serialnumber,nfa.cabinet AS cabinet,nfa.maintenance_start_time AS maintenanceStartTime,nfa.maintenance_end_time AS maintenanceEndTime,nfa.location AS location,nfa.location_name AS locationName,nfa.big_type_name AS bigTypeName,nfa.big_type_code AS bigTypeCode,nfa.middle_type_name AS middleTypeName,nfa.middle_type_code AS middleTypeCode,nfa.small_type_name AS smallTypeName,nfa.small_type_code AS smallTypeCode,nfa.contract_no AS contractNo,nfa.contract_name AS contractName,nfa.measure_unit AS measureUnit,nfa.amount AS amount,nfa.money AS money,nfa.use_period AS usePeriod,nfa.management_org_name AS managementOrgName,nfa.management_org_code AS managementOrgCode,nfa.management_user_code AS managementUserCode,nfa.management_user_name AS managementUserName,nfa.belongs AS belongs,nfa.categories AS categories,nfa.card AS card,nfa. STATUS AS status,nfa.purpose AS purpose FROM neuiot_inventory_result_temporary nirt LEFT JOIN neuiot_fixed_asset nfa ON nirt.asset_id = nfa.id WHERE nirt.create_time BETWEEN :start AND :end  AND nirt.user_id = :name", nativeQuery = true)
    List<Map<String, Object>> listByDate(@Param("start") Date start, @Param("end") Date end,
            @Param("name") String name);

    @Modifying
    @Transactional
    @Query(value = "DELETE FROM neuiot_inventory_result_temporary WHERE neuiot_inventory_result_temporary.inventory_duty_id = :id", nativeQuery = true)
    void deleteByInventoryDutyId(@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "DELETE FROM neuiot_inventory_result_temporary WHERE neuiot_inventory_result_temporary.create_time BETWEEN :start AND :end  AND neuiot_inventory_result_temporary.user_id = :name", nativeQuery = true)
    void deleteByInventoryDate(@Param("start") Date start, @Param("end") Date end,
            @Param("name") String name);
}
